/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import model.Song;
import java.util.Random;
import model.Album;

/**
 *
 * @author Jaice
 */
public class DaoSong {

    private String accountId;
    private String albumId;
    private String genreId;
    private String artistId;
    private String songId;

    public String getSongId() {
        return songId;
    }

    public void setSongId(String songId) {
        this.songId = songId;
    }

    public String getAccountId() {
        return accountId;
    }

    public void setAccountId(String accountId) {
        this.accountId = accountId;
    }

    public String getGenreId() {
        return genreId;
    }

    public void setGenreId(String genreId) {
        this.genreId = genreId;
    }

    public String getAlbumId() {
        return albumId;
    }

    public void setAlbumId(String albumId) {
        this.albumId = albumId;
    }

    public String getArtistId() {
        return artistId;
    }

    public void setArtistId(String artistId) {
        this.artistId = artistId;
    }

    public ArrayList<Song> getSongBy() throws SQLException {
        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + "s.SONG_ID, "
                + "s.TITLE, "
                + "alb.ANAME as album, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + "s.RATING, "
                + "s.DATE_RELEASED ,"
                + "s.PRICE, "
                + "s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + "from ROOT.SONG s"
                + " inner join ALBUM alb    on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    on gen.GENRE_ID     =   s.GENRE_ID"
                + " order by s.title ";
        //    PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);

        PreparedStatement ps = conn.prepareStatement(query);

        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {
            song = new Song(rs.getInt("song_id"),
                    rs.getString("title"),
                    rs.getString("album"),
                    rs.getString("genre"),
                    rs.getString("artist"),
                    rs.getDouble("rating"),
                    rs.getDate("date_released"),
                    rs.getDouble("price"),
                    rs.getString("image_location"),
                    rs.getString("lyrics"));

            list.add(song);


        }
//ps.setMaxRows(5); 

        return list;

    }

    public ArrayList<Song> retrieveGetSongBy() throws SQLException, ClassNotFoundException {
        return this.getSongBy();

    }

    public boolean addSong(Song song) throws SQLException {
        //assumes that value input is from id of dropdown list for album, genre, artist, rating

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "INSERT INTO ROOT.SONG "
                + "(    TITLE, "
                + "     ALBUM_ID,"
                + "     GENRE_ID,"
                + "     ARTIST_ID, "
                + "     RATING, "
                + "     DATE_RELEASED, "
                + "     PRICE, "
                + "     LYRICS, "
                + "     IMAGE_LOCATION) "
                + "VALUES (?,?,?,?,?,?,?,?,?)";

        PreparedStatement ps = conn.prepareStatement(query);


        ps.setString(1, song.getTitle());
        ps.setInt(2, Integer.valueOf(song.getAlbum()));
        ps.setInt(3, Integer.valueOf(song.getGenre()));
        ps.setInt(4, Integer.valueOf(song.getArtist()));
        ps.setDouble(5, Double.valueOf(song.getRating()));
        ps.setString(6, song.getDateReleasedS());
        ps.setDouble(7, song.getPrice());
        ps.setString(8, song.getLyrics());
        ps.setString(9, song.getImageLocation());

        return ps.executeUpdate() != 0 ? true : false;
    }

    public ArrayList<Song> getSongInAlbum() throws SQLException {
        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + "s.SONG_ID, "
                + "s.TITLE, "
                + "alb.ANAME as album, "
                + " s.Album_ID as albumId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + "s.RATING, "
                + "s.DATE_RELEASED ,"
                + "s.PRICE, "
                + "s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + "from ROOT.SONG s"
                + " inner join ALBUM alb    on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    on gen.GENRE_ID     =   s.GENRE_ID "
                + " where  s.Album_ID = ? "
                + " order by s.title ";
        //    PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, this.albumId);
        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {
            song = new Song(rs.getInt("song_id"),
                    rs.getString("title"),
                    rs.getString("album"),
                    rs.getString("genre"),
                    rs.getString("artist"),
                    rs.getDouble("rating"),
                    rs.getDate("date_released"),
                    rs.getDouble("price"),
                    rs.getString("image_location"),
                    rs.getString("lyrics"));

            list.add(song);


        }


        return list;

    }

    public ArrayList<Song> retrieveGetSongInAlbum() throws SQLException, ClassNotFoundException {
        return this.getSongInAlbum();

    }

    public ArrayList<Song> getSongByGenre() throws SQLException {
        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + "s.SONG_ID, "
                + "s.TITLE, "
                + "alb.ANAME as album, "
                + " s.genre_ID as genreId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + "s.RATING, "
                + "s.DATE_RELEASED ,"
                + "s.PRICE, "
                + "s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + "from ROOT.SONG s"
                + " inner join ALBUM alb    on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    on gen.GENRE_ID     =   s.GENRE_ID "
                + " where  s.Genre_ID = ? "
                + " order by s.title ";
        //      PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);



        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, this.genreId);

        ResultSet rs = ps.executeQuery();
////if was able to get a row
        while (rs.next()) {
            song = new Song(rs.getInt("song_id"),
                    rs.getString("title"),
                    rs.getString("album"),
                    rs.getString("genre"),
                    rs.getString("artist"),
                    rs.getDouble("rating"),
                    rs.getDate("date_released"),
                    rs.getDouble("price"),
                    rs.getString("image_location"),
                    rs.getString("lyrics"));

            list.add(song);


        }


        return list;

    }

    public ArrayList<Song> retrieveGetSongByGenre() throws SQLException, ClassNotFoundException {
        return this.getSongByGenre();

    }

    public ArrayList<Song> getSongByArtist() throws SQLException {
        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + " s.SONG_ID, "
                + " s.TITLE, "
                + " alb.ANAME as album, "
                + " s.artist_ID as artistId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + " s.RATING, "
                + " s.DATE_RELEASED ,"
                + " s.PRICE, "
                + " s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + " from ROOT.SONG s"
                + " inner join ALBUM alb    on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    on gen.GENRE_ID     =   s.GENRE_ID "
                + " where  s.artist_ID = ? "
                + " order by s.title ";
        //    PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, this.artistId);
        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {
            song = new Song(rs.getInt("song_id"),
                    rs.getString("title"),
                    rs.getString("album"),
                    rs.getString("genre"),
                    rs.getString("artist"),
                    rs.getDouble("rating"),
                    rs.getDate("date_released"),
                    rs.getDouble("price"),
                    rs.getString("image_location"),
                    rs.getString("lyrics"));

            list.add(song);


        }


        return list;

    }

    public ArrayList<Song> retrieveGetSongByArtist() throws SQLException, ClassNotFoundException {
        return this.getSongByArtist();

    }

    public ArrayList<Song> getSongByShopping() throws SQLException {
        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + " s.SONG_ID, "
                + " s.TITLE, "
                + " alb.ANAME as album, "
                + " s.artist_ID as artistId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + " s.RATING, "
                + " s.DATE_RELEASED ,"
                + " s.PRICE, s.PRICE, "
                + " s.image_location,"
                + " s.lyrics,"
                + " tl.reviewed "
                + " from ROOT.SONG s "
                + " inner join ALBUM alb    "
                + "     on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   "
                + "     on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    "
                + "     on gen.GENRE_ID     =   s.GENRE_ID "
                + " inner join TRANSACTION_LOG tl "
                + "     on s.SONG_ID = tl.SONG_ID "
                + " where  tl.USER_ID = ? "
                + " order by s.title  ";
        //    PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, this.accountId);


        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {
            song = new Song(rs.getInt("song_id"),
                    rs.getString("title"),
                    rs.getString("album"),
                    rs.getString("genre"),
                    rs.getString("artist"),
                    rs.getDouble("rating"),
                    rs.getDate("date_released"),
                    rs.getDouble("price"),
                    rs.getString("image_location"),
                    rs.getString("lyrics"),
                    rs.getString("reviewed"));

            list.add(song);


        }


        return list;

    }

    public ArrayList<Song> retrieveGetSongByShopping() throws SQLException, ClassNotFoundException {
        return this.getSongByShopping();

    }

    public Song getRandomSpecial() throws SQLException {
        ArrayList<Song> list = this.getSongBy();

        Random randomGenerator = new Random();

        int randomInt = 1;
        if (list.size() > 0) {
            for (int idx = 1; idx <= 10; ++idx) {
                randomInt = randomGenerator.nextInt(list.size() - 1);
            }

        }

        return list.get(randomInt);

    }

    public ArrayList<Song> getTopNewReleases() throws SQLException {

        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();



        Calendar now = Calendar.getInstance();

     

        String querytop = "select "
                + "count(*) as sales,"
                + " s.song_id "
                + "from ROOT.TRANSACTION_LOG tl "
                + "inner join Song s on s.SONG_ID = tl.SONG_ID "
                + "where s.DATE_RELEASED >=? and "
                + "s.DATE_RELEASED <=?  "
                + "group by s.song_id  "
                + "order by sales desc";


        String querysong = "select "
                + " s.SONG_ID, "
                + " s.TITLE, "
                + " alb.ANAME as album, "
                + " s.artist_ID as artistId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + " s.RATING, "
                + " s.DATE_RELEASED ,"
                + " s.PRICE, s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + " from ROOT.SONG s "
                + " inner join ALBUM alb    "
                + "     on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   "
                + "     on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    "
                + "     on gen.GENRE_ID     =   s.GENRE_ID "
                + " inner join TRANSACTION_LOG tl "
                + "     on s.SONG_ID = tl.SONG_ID "
                + " where  s.song_id = ? ";

        PreparedStatement pstop = conn.prepareStatement(querytop);

        //get date start 
        pstop.setString(1, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));
        //add one week
        now.add(Calendar.DATE, 6);
        //get date end
        pstop.setString(2, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));


        //    PreparedStatement ps = conn.prepareStatement(
        ResultSet rstop = pstop.executeQuery();
        ResultSet rssong = null;
        //  System.out.println(query);

        PreparedStatement pssong = conn.prepareStatement(querysong);


        pssong.setMaxRows(10);
        while (rstop.next()) {
            pssong.setString(1, rstop.getString("song_id"));
            rssong = pssong.executeQuery();


            rssong.next();

            //   while (rssong.next()) {
            song = new Song(rssong.getInt("song_id"),
                    rssong.getString("title"),
                    rssong.getString("album"),
                    rssong.getString("genre"),
                    rssong.getString("artist"),
                    rssong.getDouble("rating"),
                    rssong.getDate("date_released"),
                    rssong.getDouble("price"),
                    rssong.getString("image_location"),
                    rssong.getString("lyrics"));

            list.add(song);

            //   }

        }


        while (list.size() > 10) {
            list.remove(list.size() - 1);
        }



        return list;



    }

    public ArrayList<Song> retrieveGetTopNewReleases() throws SQLException {
        return this.getTopNewReleases();
    }

    public ArrayList<Song> getTopSalesSong() throws SQLException {

        ArrayList<Song> list = new ArrayList<Song>();
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();



        Calendar now = Calendar.getInstance();



        String querytop = " select   "
                + " count(*) as sales,   "
                + " s.song_id    "
                + " from ROOT.TRANSACTION_LOG tl     "
                + " inner join Song s on s.SONG_ID = tl.SONG_ID     "
                + " where tl.DATE_CREATED >=? and      "
                + " tl.DATE_CREATED <=?       "
                + " group by s.song_id        "
                + " order by sales desc ";


        String querysong = "select "
                + " s.SONG_ID, "
                + " s.TITLE, "
                + " alb.ANAME as album, "
                + " s.artist_ID as artistId, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + " s.RATING, "
                + " s.DATE_RELEASED ,"
                + " s.PRICE, s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + " from ROOT.SONG s "
                + " inner join ALBUM alb    "
                + "     on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   "
                + "     on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    "
                + "     on gen.GENRE_ID     =   s.GENRE_ID "
                + " inner join TRANSACTION_LOG tl "
                + "     on s.SONG_ID = tl.SONG_ID "
                + " where  s.song_id = ? ";

        PreparedStatement pstop = conn.prepareStatement(querytop);

        //get date start 
        pstop.setString(1, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));
       // System.out.println(now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));

        //add one week
        now.add(Calendar.DATE, 6);
        //get date end
        pstop.setString(2, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));
      //  System.out.println(now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));


        //    PreparedStatement ps = conn.prepareStatement(
        ResultSet rstop = pstop.executeQuery();
        ResultSet rssong = null;
        //  System.out.println(query);

        PreparedStatement pssong = conn.prepareStatement(querysong);


        pssong.setMaxRows(10);
        while (rstop.next()) {
            pssong.setString(1, rstop.getString("song_id"));
            rssong = pssong.executeQuery();


            rssong.next();

            //   while (rssong.next()) {
            song = new Song(rssong.getInt("song_id"),
                    rssong.getString("title"),
                    rssong.getString("album"),
                    rssong.getString("genre"),
                    rssong.getString("artist"),
                    rssong.getDouble("rating"),
                    rssong.getDate("date_released"),
                    rssong.getDouble("price"),
                    rssong.getString("image_location"),
                    rssong.getString("lyrics"));

            list.add(song);

            //   }

        }


        while (list.size() > 10) {
            list.remove(list.size() - 1);
        }



        return list;



    }

    public ArrayList<Song> retrieveGetTopSalesSong() throws SQLException {
        return this.getTopSalesSong();
    }

    public ArrayList<Album> getTopSalesAlbum() throws SQLException {
        ArrayList<Album> list = new ArrayList<Album>();

        Album album = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();



        Calendar now = Calendar.getInstance();



        String querytop = " select   "
                + " count(*) as sales,   "
                + " a.ALBUM_ID    "
                + " from ROOT.TRANSACTION_LOG tl     "
                + " inner join Album a on a.ALBUM_ID = tl.ALBUM_ID     "
                + " where tl.DATE_CREATED >=? and      "
                + " tl.DATE_CREATED <=?       "
                + " group by a.ALBUM_ID        "
                + " order by sales desc ";


        String queryalbum = "select "
                + " alb.ALBUM_ID, "
                + " alb.ANAME as albumname, "
                + " alb.RATING, "
                + " alb.DATE_RELEASED, "
                + " alb.PRICE, "
                + " alb.IMAGE_LOCATION "
                + " from ROOT.ALBUM alb "
                + " where alb.album_id = ?"
                + " order by alb.ANAME ";

        PreparedStatement pstop = conn.prepareStatement(querytop);

        //get date start 
        pstop.setString(1, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));
        //add one week
        now.add(Calendar.DATE, 6);
        //get date end
        pstop.setString(2, now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH)+1) + "-" + now.get(Calendar.DATE));


        //    PreparedStatement ps = conn.prepareStatement(
        ResultSet rstop = pstop.executeQuery();
        ResultSet rsalbum = null;
        //  System.out.println(query);

        PreparedStatement pssong = conn.prepareStatement(queryalbum);


        pssong.setMaxRows(10);
        while (rstop.next()) {
            pssong.setString(1, rstop.getString("album_id"));
            rsalbum = pssong.executeQuery();


            rsalbum.next();

            //   while (rssong.next()) {
            album = new Album(rsalbum.getInt("ALBUM_ID"),
                    rsalbum.getString("AlbumNAME"),
                    rsalbum.getDouble("RATING"),
                    rsalbum.getDate("DATE_RELEASED"),
                    rsalbum.getDouble("PRICE"),
                    rsalbum.getString("IMAGE_LOCATION"));

            list.add(album);

            //   }

        }


        while (list.size() > 10) {
            list.remove(list.size() - 1);
        }






        return list;
    }

    public ArrayList<Album> retrieveGetTopSalesAlbum() throws SQLException {
        return this.getTopSalesAlbum();
    }

    public Song getSongLyrics() throws SQLException {
        Song song = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + "s.SONG_ID, "
                + "s.TITLE, "
                + "alb.ANAME as album, "
                + " gen.GENRE as genre, "
                + " art.ANAME as artist, "
                + "s.RATING, "
                + "s.DATE_RELEASED ,"
                + "s.PRICE, "
                + "s.PRICE, "
                + " s.image_location,"
                + " s.lyrics "
                + "from ROOT.SONG s"
                + " inner join ALBUM alb    on alb.ALBUM_ID     =   s.ALBUM_ID "
                + " inner join ARTIST art   on art.ARTIST_ID    =   s.ARTIST_ID "
                + " inner join GENRE gen    on gen.GENRE_ID     =   s.GENRE_ID"
                + " where s.song_id = ?"
                + "order by s.title ";
        //    PreparedStatement ps = conn.prepareStatement(


        //  System.out.println(query);

        PreparedStatement ps = conn.prepareStatement(query);

        ps.setString(1, this.songId);

        ResultSet rs = ps.executeQuery();
//if was able to get a row
        rs.next();

        song = new Song(rs.getInt("song_id"),
                rs.getString("title"),
                rs.getString("album"),
                rs.getString("genre"),
                rs.getString("artist"),
                rs.getDouble("rating"),
                rs.getDate("date_released"),
                rs.getDouble("price"),
                rs.getString("image_location"),
                rs.getString("lyrics"));






        return song;
    }

    public boolean updateSong(Song song) throws SQLException {
        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "UPDATE ROOT.SONG "
                + " SET TITLE = ?, "
                + " ALBUM_ID = ?, "
                + " GENRE_ID = ?, "
                + " ARTIST_ID = ?, "
                + " RATING = ?, "
                + " DATE_RELEASED = ?, "
                + " PRICE = ?, "
                + " LYRICS = ? "
                + " WHERE SONG_ID = ?";

        PreparedStatement ps = conn.prepareStatement(query);


        ps.setString(1, song.getTitle());
        ps.setInt(2, Integer.valueOf(song.getAlbum()));
        ps.setInt(3, Integer.valueOf(song.getGenre()));
        ps.setInt(4, Integer.valueOf(song.getArtist()));
        ps.setDouble(5, Double.valueOf(song.getRating()));
        ps.setString(6, song.getDateReleasedS());
        ps.setDouble(7, song.getPrice());
        ps.setString(8, song.getLyrics());
        ps.setInt(9, song.getSongId());

        return ps.executeUpdate() != 0 ? true : false;
    }
}
